// 用户单词本
import 'package:sqlite3/sqlite3.dart';
import '../model/model_user_word.dart';
import '../tools/tools_database.dart';

class DaoUserWord {
  static Future<int> insert(ModelUserWord l) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
    INSERT INTO 
      p001_user_word
      (language_id,word,type,
      start_review_time,last_review_time,review_progress,deferred,
      say_right_qty,input_right_qty,write_right_qty,
      say_wrong_qty,input_wrong_qty,write_wrong_qty,
      listen_qty,read_qty,
      gmt_create,gmt_modified,remark)
    VALUES
      (?,?,?,
      ?,?,?,?,
      ?,?,?,
      ?,?,?,
      ?,?,
      ?,?,?
      )
    ;
    ''';
    final stmt = db.prepare(sql);
    DateTime now = DateTime.now();
    int gmt_create = now.millisecondsSinceEpoch;
    stmt
      ..execute([
        l.languageId,
        l.word,
        l.type,
        l.startReviewTime,
        l.lastReviewTime,
        l.reviewProgress,
        l.deferred,
        l.sayRightQty,
        l.inputRightQty,
        l.writeRightQty,
        l.sayWrongQty,
        l.inputWrongQty,
        l.writeRightQty,
        l.listenQty,
        l.readQty,
        gmt_create,
        gmt_create,
        l.remark
      ]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<int> deleteById(int id) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      delete from 
        p001_user_word 
      where 
        id = ?;
    ''';
    final stmt = db.prepare(sql);
    stmt..execute([id]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<int> updateById(ModelUserWord l) async {
    print(l);
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      update 
        p001_user_word
      set
        language_id=?,word=?,type=?,
        start_review_time=?,last_review_time=?,
        review_progress=?,deferred=?,
        say_right_qty=?,input_right_qty=?,write_right_qty=?,
        say_wrong_qty=?,input_wrong_qty=?,write_wrong_qty=?,
        listen_qty=?,read_qty=?,
        gmt_modified = ?,
        remark = ?
      where
        id = ?
      ;
    ''';

    final stmt = db.prepare(sql);
    try {
      int gmt_modified = DateTime.now().millisecondsSinceEpoch;
      stmt
        ..execute([
          l.languageId,
          l.word,
          l.type,
          l.startReviewTime,
          l.lastReviewTime,
          l.reviewProgress,
          l.deferred,
          l.sayRightQty,
          l.inputRightQty,
          l.writeRightQty,
          l.sayWrongQty,
          l.inputWrongQty,
          l.writeRightQty,
          l.listenQty,
          l.readQty,
          gmt_modified,
          l.remark,
          l.id
        ]);
      print("执行成功");
    } catch (e) {
      print("出现异常");
      print(e);
    }

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static Future<List<ModelUserWord>> findAll() async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_word
      ;
    ''';
    ResultSet rs = db.select(sql);
    Iterator<Row> i = rs.iterator;
    List<ModelUserWord> list = [];
    while (i.moveNext()) {
      Row r = i.current;
      print(r);

      ModelUserWord l = new ModelUserWord();
      l.id = r["id"];
      l.languageId = r["language_id"];
      l.word = r["word"];
      l.type = r["type"];

      l.startReviewTime = r["start_review_time"];
      l.lastReviewTime = r["last_review_time"];
      l.reviewProgress = r["review_progress"];
      l.deferred = r["deferred"];

      l.sayRightQty = r["say_right_qty"];
      l.inputRightQty = r["input_right_qty"];
      l.writeRightQty = r["write_right_qty"];

      l.sayWrongQty = r["say_wrong_qty"];
      l.inputWrongQty = r["input_wrong_qty"];
      l.writeWrongQty = r["write_wrong_qty"];

      l.listenQty = r["listen_qty"];
      l.readQty = r["read_qty"];

      l.gmtCreate = r["gmt_create"];
      l.gmtModified = r["gmt_modified"];
      l.remark = r["remark"];

      list.add(l);
    }

    // 3. 关闭连接
    db.dispose();
    return list;
  }

  static Future<ModelUserWord?> findById(int id) async {
    ModelUserWord? m = null;

    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_word
      where
        id = ?
      ;
    ''';
    ModelUserWord? l = null;
    ResultSet rs = db.select(sql, [id]);
    Iterator<Row> i = rs.iterator;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);

      l = new ModelUserWord();
      l.id = r["id"];
      l.languageId = r["language_id"];
      l.word = r["word"];
      l.type = r["type"];

      l.startReviewTime = r["start_review_time"];
      l.lastReviewTime = r["last_review_time"];
      l.reviewProgress = r["review_progress"];
      l.deferred = r["deferred"];

      l.sayRightQty = r["say_right_qty"];
      l.inputRightQty = r["input_right_qty"];
      l.writeRightQty = r["write_right_qty"];

      l.sayWrongQty = r["say_wrong_qty"];
      l.inputWrongQty = r["input_wrong_qty"];
      l.writeWrongQty = r["write_wrong_qty"];

      l.listenQty = r["listen_qty"];
      l.readQty = r["read_qty"];

      l.gmtCreate = r["gmt_create"];
      l.gmtModified = r["gmt_modified"];
      l.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    return l;
  }

  static Future<ModelUserWord?> findByWord(String word) async {
    ModelUserWord? m = null;

    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_word
      where
        word = ?
      ;
    ''';
    ModelUserWord? l = null;
    ResultSet rs = db.select(sql, [word]);
    Iterator<Row> i = rs.iterator;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);

      l = new ModelUserWord();
      l.id = r["id"];
      l.languageId = r["language_id"];
      l.word = r["word"];
      l.type = r["type"];

      l.startReviewTime = r["start_review_time"];
      l.lastReviewTime = r["last_review_time"];
      l.reviewProgress = r["review_progress"];
      l.deferred = r["deferred"];

      l.sayRightQty = r["say_right_qty"];
      l.inputRightQty = r["input_right_qty"];
      l.writeRightQty = r["write_right_qty"];

      l.sayWrongQty = r["say_wrong_qty"];
      l.inputWrongQty = r["input_wrong_qty"];
      l.writeWrongQty = r["write_wrong_qty"];

      l.listenQty = r["listen_qty"];
      l.readQty = r["read_qty"];

      l.gmtCreate = r["gmt_create"];
      l.gmtModified = r["gmt_modified"];
      l.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    return l;
  }

  static Future<List<ModelUserWord>> findByQueryString(String s) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_word
    ''' +
        s;
    ResultSet rs = db.select(sql);
    Iterator<Row> i = rs.iterator;
    List<ModelUserWord> list = [];
    while (i.moveNext()) {
      Row r = i.current;
      print(r);

      ModelUserWord l = new ModelUserWord();
      l.id = r["id"];
      l.languageId = r["language_id"];
      l.word = r["word"];
      l.type = r["type"];

      l.startReviewTime = r["start_review_time"];
      l.lastReviewTime = r["last_review_time"];
      l.reviewProgress = r["review_progress"];
      l.deferred = r["deferred"];

      l.sayRightQty = r["say_right_qty"];
      l.inputRightQty = r["input_right_qty"];
      l.writeRightQty = r["write_right_qty"];

      l.sayWrongQty = r["say_wrong_qty"];
      l.inputWrongQty = r["input_wrong_qty"];
      l.writeWrongQty = r["write_wrong_qty"];

      l.listenQty = r["listen_qty"];
      l.readQty = r["read_qty"];

      l.gmtCreate = r["gmt_create"];
      l.gmtModified = r["gmt_modified"];
      l.remark = r["remark"];

      list.add(l);
    }

    // 3. 关闭连接
    db.dispose();
    return list;
  }

  static Future<int> countByQueryString(String s) async {
    // 1. 连接数据库
    Database db = await ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        count(*)
      from
        p001_user_word
    ''' +
        s;
    ResultSet rs = db.select(sql);
    Iterator<Row> i = rs.iterator;
    int count = 0;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);
      print(r["count(*)"]);
      count = r["count(*)"];
    }

    // 3. 关闭连接
    db.dispose();
    return count;
  }
}
